-- @owner: lihongji
-- @date: 2022/07/18
-- @testpoint: 临时表中使用ntile()函数

--step1:建表;expect:成功
drop table if exists t_ustore_ntile_0052_01;
create table t_ustore_ntile_0052_01(
col_1 bigint, col_2 timestamp without time zone, col_3 bool,col_4 decimal,col_5 clob ,
col_6 interval day to second ,col_7 nchar(30) ,col_8 blob ,col_9 raw(100),col_10 date,
col_11 blob ,col_12 bytea
) with (storage_type=ustore);

--step2:创建序列;expect:成功
drop sequence if exists sq_ustore_ntile_0052;
create sequence sq_ustore_ntile_0052 increment by 1 start with 10000;

--step3:清空数据,插入数据;expect:成功
truncate table t_ustore_ntile_0052_01;
begin
 for j in 1..6 loop
  for i in 1..15 loop
   insert into t_ustore_ntile_0052_01 values(
   sq_ustore_ntile_0052.nextval,to_timestamp('12-sep-2014'),true,3.1415926+sq_ustore_ntile_0052.nextval,lpad('abc','5000','a@123&^%djgk'),
   (interval '4 5:12:10.222' day to second(3)),
   rpad('abc','20','e'),
   lpad('10',5000,'01010')::blob,rpad('0f',100,'aadb9')::raw,
   to_timestamp('12-sep-2014'),
   lpad('10',i,'01010')::blob,'010101111111100000000000000'
   );
  end loop;
 end loop;
end;
/

--step4:创建索引;expect:成功
drop index if exists i_ustore_ntile_0052;
create index i_ustore_ntile_0052 on t_ustore_ntile_0052_01(col_2,col_7);

--step5:创建视图;expect:成功
drop view if exists v_ustore_ntile_0052;
create view v_ustore_ntile_0052 as select col_1,col_5 from t_ustore_ntile_0052_01;


--step6:建表;expect:成功
drop table if exists t_ustore_ntile_0052_02;
create global temporary table t_ustore_ntile_0052_02 with (storage_type=ustore) on commit preserve rows as select * from t_ustore_ntile_0052_01;

--step7:查询;expect:成功
select col_2,col_3,col_9, ntile(200) over(partition by col_2,col_3,col_9 order by col_6,col_7,col_8) f1 from t_ustore_ntile_0052_02 limit 10;

--step8:清理环境;expect:成功
drop view if exists v_ustore_ntile_0052;
drop index if exists i_ustore_ntile_0052;
drop table if exists t_ustore_ntile_0052_01;
drop table if exists t_ustore_ntile_0052_02;
drop sequence if exists sq_ustore_ntile_0052;
